﻿using System;
using System.Data;
using System.Data.DBLib;
using SysComm;

namespace SysOperationLayer.DBHandle
{
    public class CountriesDB : DBBase
    {
        public CountriesDB()
        {
        }

        public CountriesDB(CSqlDB objDB)
        {
            m_objDB = objDB;
        }

        public DataTable GetInfo()
        {
            if (ConnectDB())
            {
                string strSql = "SELECT Country, EnglishName, ChineseName, PCTYN, CreateBy, CreateDate, UpdateBy, UpdateDate, OwnerGroupID,IsDelete FROM Country WHERE IsDelete = 0";
                strSql += " Union Select '', '', '', '', '', getdate(), '', getdate(), 0, 0";
                return m_objDB.getDataTable(strSql, "Country");
            }

            return null;
        }

        public DataTable getCountriesList(string country, string engName, string chnName, int isDelete)
        {
            if (ConnectDB())
            {
                string strSql = "select * from Country where 1=1";

                if (country != "" && country != null)
                {
                    strSql += " and Country like '%" + country + "%'";
                }

                if (engName != "" && engName != null)
                {
                    strSql += " and EnglishName like '%" + engName + "%'";
                }

                if (chnName != "" && chnName != null)
                {
                    strSql += " and EnglishName like '%" + engName + "%'";
                }

                strSql += " and IsDelete=" + isDelete;

                return m_objDB.getDataTable(strSql, "Country");
            }

            m_strMessage = "Cannot Connect to the Database!";

            return null;
        }

        public DataRow GetItem(string strCountry)
        {
            if (ConnectDB())
            {
                string strSql = "select * from Country where Country = '";
                strSql += strCountry;
                strSql += "'";
                return m_objDB.getDataRow(strSql, "Country");
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return null;
        }

        public int Insert(string country, string engName, string chnName, int pct, string strUserID, string strUserIP)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "insert into Country(Country, EnglishName, ChineseName, PCTYN";
                strSql += ", CreateIp, CreateBy, CreateDate, UpdateIp, UpdateBy, UpdateDate)";
                strSql += " values('" + country + "','" + engName + "','" + chnName + "'," + pct + ",'";
                strSql += strUserIP + "','" + strUserID + "',getDate(),'" + strUserIP + "','" + strUserID + "',getDate())";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int Update(string strCountry, string engName, string chnName, int pct, string strUserID, string strUserIP)
        {
            if (IsEmpty(strCountry))
            {
                m_strMessage = "Country Code Is Empty!";
            }
            else if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "update Country set ";
                strSql += " EnglishName = '";
                strSql += engName;
                strSql += "', ChineseName = '";
                strSql += chnName;
                strSql += "', PCTYN = ";
                strSql += pct;
                strSql += ", UpdateBy = '";
                strSql += strUserID;
                strSql += "', UpdateIp = '";
                strSql += strUserIP;
                strSql += "', UpdateDate = getDate()";
                strSql += " where Country = '";
                strSql += strCountry;
                strSql += "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }
        
        public int Delete(string strCountry, string strUserID, string strUserIP)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "update Country set ";
                strSql += " isDelete=1";
                strSql += ", DeleteBy='" + strUserID + "'";
                strSql += ", DeleteIP='" + strUserIP + "'";
                strSql += ", DeleteDate=getDate()";
                strSql += " where Country='" + strCountry + "'";
                m_objDB.updateData(strSql, objError);
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int Trash(string strCountry)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "delete from Country where Country='" + strCountry + "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }

        public int Restore(string strCountry)
        {
            if (ConnectDB())
            {
                CDBError objError = new CDBError();
                string strSql = "update Country set isDelete=0 where Country='" + strCountry + "'";
                m_objDB.updateData(strSql, objError);
                m_strMessage = objError.Message;
                return objError.RetCode;
            }
            else
            {
                m_strMessage = "Cannot Connect to the Database!";
            }

            return -1;
        }
    }
}
